﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using OfficeOpenXml;

using AtletIS.Shared.Data.aDbDataSetTableAdapters;
using AtletIS.Shared.Entities;
using AtletIS.Shared.Helpers;

namespace AtletIS.Shared.Helpers
{
    public static class SignupsExportHelper
    {
        public static void ExportCompetition(int competitionId)
        {
            FileInfo fi = new FileInfo("AtletISEntries.xlsx");
            
            if (fi.Exists)
                fi.Delete();

            ExcelPackage pkg = new ExcelPackage(fi);
            ExportPerformances(competitionId, pkg);
            
            // Save, run
            pkg.Save();
            System.Diagnostics.Process.Start(fi.FullName);

        }

        private static void ExportCompetition(int competitionId, ExcelWorksheet ws)
        {
            // Header
            ws.Cells["A1"].Value = "ID";
            ws.Cells["B1"].Value = "Name";
            ws.Cells["C1"].Value = "City";
            ws.Cells["D1"].Value = "Organiser";
            ws.Cells["E1"].Value = "DateFrom";
            ws.Cells["F1"].Value = "DateTo";
            ws.Cells["G1"].Value = "Deadline";
            ws.Cells["H1"].Value = "ClubsCountries";
            ws.Cells["I1"].Value = "Lanes";
            ws.Cells["J1"].Value = "LanesQuality";
            ws.Cells["A1:J1"].Style.Font.Bold = true;

            CompetitionEntity competition = CompetitionHelper.ConvertToEntity(DatabaseHelper.GetCompetitionsById(competitionId).Rows[0]);
            ws.Cells["A2"].Value = competition.ID;
            ws.Cells["B2"].Value = competition.Name;
            ws.Cells["C2"].Value = competition.City;
            ws.Cells["D2"].Value = competition.Organiser;
            ws.Cells["E2"].Value = Convert.ToDateTime(competition.DateFrom).ToShortDateString();
            ws.Cells["F2"].Value = Convert.ToDateTime(competition.DateTo).ToShortDateString();
            ws.Cells["G2"].Value = Convert.ToDateTime(competition.Deadline).ToShortDateString();
            ws.Cells["H2"].Value = competition.ClubsCountries;
            ws.Cells["I2"].Value = competition.LanesCount;

            string lq = string.Empty;
            foreach (string s in competition.LanesQuality)
                lq += s + ",";

            ws.Cells["J2"].Value = lq.TrimEnd(',');
            
            // Fit columns
            ws.Cells["A1:J2"].AutoFitColumns();
        }

        private static void ExportDisciplineList(int competitionId, ExcelWorksheet ws)
        {
            // Header
            ws.Cells["A1"].Value = "ID";
            ws.Cells["B1"].Value = "Name";
            ws.Cells["C1"].Value = "Order";
            ws.Cells["D1"].Value = "CompetitionId";
            ws.Cells["E1"].Value = "DisciplineId";
            ws.Cells["F1"].Value = "CategoryId";
            ws.Cells["G1"].Value = "StartTime";
            ws.Cells["H1"].Value = "DisciplineCode";
            ws.Cells["A1:H1"].Style.Font.Bold = true;

            DataTable dtDisciplineList = DatabaseHelper.GetDisciplineListByCompetitionIdActive(competitionId); // taDisciplineList.GetByCompetitionId(competitionId);
            int row = 2;
            foreach (DataRow item in dtDisciplineList.Select(null, "Order")) 
            {
                ws.Cells["A" + row.ToString()].Value = item["ID"];
                ws.Cells["B" + row.ToString()].Value = item["Name"];
                ws.Cells["C" + row.ToString()].Value = item["Order"];
                ws.Cells["D" + row.ToString()].Value = item["CompetitionId"];
                ws.Cells["E" + row.ToString()].Value = item["DisciplineId"];
                ws.Cells["F" + row.ToString()].Value = item["CategoryId"];
                ws.Cells["G" + row.ToString()].Value = item["StartTime"];
                ws.Cells["H" + row.ToString()].Value = item["DisciplineCode"];

                row += 1;
            }

            // Fit columns
            ws.Cells["A1:H" + row.ToString()].AutoFitColumns();
        }

        private static void ExportPerformances(int competitionId, ExcelPackage pkg)
        {
            DataTable dtDisciplineList = DatabaseHelper.GetDisciplineListByCompetitionIdActive(competitionId); // taDisciplineList.GetByCompetitionId(competitionId);
            foreach (DataRow drDisciplineList in dtDisciplineList.Select(null, "Order"))
            {
                ExcelWorksheet ws = pkg.Workbook.Worksheets.Add(drDisciplineList["Name"].ToString());

                // Header
                ws.Cells["A1"].Value = "DISCIPLINA";
                ws.Cells["B1"].Value = "MENO";
                ws.Cells["C1"].Value = "PRIEZVISKO";
                ws.Cells["D1"].Value = "DAT_NAR";
                ws.Cells["E1"].Value = "KLUB";
                ws.Cells["F1"].Value = "KRAJINA";
                ws.Cells["G1"].Value = "ST_CISLO";
                ws.Cells["H1"].Value = "SB";
                ws.Cells["I1"].Value = "sSB";
                ws.Cells["J1"].Value = "PB";
                ws.Cells["K1"].Value = "sPB";
                ws.Cells["L1"].Value = "SMS";
                ws.Cells["A1:L1"].Style.Font.Bold = true;

                DataTable dtPerformances = DatabaseHelper.GetPerformancesByDisciplineListId(Convert.ToInt32(drDisciplineList["ID"])); 
                // taPerformances.GetByDisciplineListId(Convert.ToInt32(drDisciplineList["ID"]));

                int row = 2;
                foreach (var item in dtPerformances.Select(null, "CountryId, Club, Surname, FirstName"))
                {
                    ws.Cells["A" + row.ToString()].Value = drDisciplineList["Name"];
                    ws.Cells["B" + row.ToString()].Value = item["FirstName"];
                    ws.Cells["C" + row.ToString()].Value = item["Surname"];
                    ws.Cells["D" + row.ToString()].Value = Convert.ToDateTime(item["Birthdate"]).ToShortDateString();
                    ws.Cells["E" + row.ToString()].Value = item["Club"];
                    ws.Cells["F" + row.ToString()].Value = item["CountryId"];
                    ws.Cells["G" + row.ToString()].Value = item["StartNumber"];
                    ws.Cells["H" + row.ToString()].Value = item["SB"];
                    ws.Cells["I" + row.ToString()].Value = PrintHelper.PrintResult(item["SB"].ToString(), Convert.ToInt32(drDisciplineList["DisciplineCode"]));
                    ws.Cells["J" + row.ToString()].Value = item["PB"];
                    ws.Cells["K" + row.ToString()].Value = PrintHelper.PrintResult(item["PB"].ToString(), Convert.ToInt32(drDisciplineList["DisciplineCode"]));
                    ws.Cells["L" + row.ToString()].Value = item["SMS"];

                    row += 1;
                }

                // Fit columns
                ws.Cells["A1:L" + row.ToString()].AutoFitColumns();
            }
        }


    }
}
